import com.sun.pojo.Student;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by M_WBCG on 2017/7/14.
 */
public class ReadExcelForXSSF {
    public List<Student> read() {
        String filePath = "E:\\Mybatis\\Experiment2_Mybatis\\springMVC\\SpringMVC_POI\\src\\test\\java\\学生成绩表.xlsx";
        File file = new File(filePath);
        InputStream inputStream = null;
        Workbook workbook = null;
        List<Student> list = new ArrayList<Student>();
       /* Student pojo=new Student();*/
        //判断是否为excel类型文件
        if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
        {
            System.out.println("文件不是excel类型");
        }

        try {
                inputStream = new FileInputStream(file);
                workbook = WorkbookFactory.create(inputStream);
                inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum()+1;
            //工作表的列
            Row row = sheet.getRow(0);
            //总列数
            int colLength = row.getLastCellNum();
            //得到指定的单元格
            Cell cell = row.getCell(0);
            //得到单元格样式
            CellStyle cellStyle = cell.getCellStyle();
            System.out.println("行数：" + rowLength + ",列数：" + colLength);
            for (int i = 1; i < rowLength; i++) {
                Student student=new Student();
                row = sheet.getRow(i);
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    //Excel数据Cell有不同的类型，当我们试图从一个数字类型的Cell读取出一个字符串时就有可能报异常：
                    //Cannot get a STRING value from a NUMERIC cell
                    //将所有的需要读的Cell表格设置为String格式
                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);

                    }
                    //对Excel进行修改
                    /*if (i > 0 && j == 1)
                        cell.setCellValue("1000");*/
                    System.out.print(cell.getStringCellValue() + "\t");
                    switch (j){
                        case 0:student.setId(cell.getStringCellValue());break;
                        case 1:student.setName(cell.getStringCellValue());break;
                        case 2:student.setChineseScore(Double.parseDouble(cell.getStringCellValue()));break;
                        case 3:student.setMathScore(Double.parseDouble(cell.getStringCellValue()));break;
                        case 4:student.setEnglishScore(Double.parseDouble(cell.getStringCellValue()));break;
                        case 5:student.setPhysicalScore(Double.parseDouble(cell.getStringCellValue()));break;
                        case 6:student.setChemicalScore(Double.parseDouble(cell.getStringCellValue()));break;
                        case 7:student.setGeographyScore(Double.parseDouble(cell.getStringCellValue()));break;

                    }

                }
                list.add(student);
                System.out.println(student.toString());

                System.out.println();
            }

            //将修改好的数据保存
            OutputStream out = new FileOutputStream(file);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (Student student1:list){
            System.out.println(student1.toString());
            System.out.println();
        }
        return list;
    }

    public static void main(String[] args) {
        new ReadExcelForXSSF().read();
    }
}